<?php 
class report extends Controller {

	function report()
	{
		parent::Controller();
		date_default_timezone_set ("Asia/Bangkok"); 	
		if(!$this->user->is_login())
		{
			redirect('login', 'location'); 
		}
		$this->user->protection();
		$this->form_validation->set_message('required', 'กรุณาระบุ %s');
		$this->form_validation->set_message('is_natural_no_zero', 'กรุณาระบุ %s');
		//$this->output->enable_profiler(TRUE);
	}
	function inventory_list_manager_print($category_id,$date1,$date2)
	{
		$this->template->set("head_text", $this->user->get_h1());
		
		
		
		
		
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date1"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date'";
			$data["date1"] = $date;
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date2"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date'";
		
			$data["date2"] = $date;
			$data["type_id"] = $_POST["type_id"];
		
		
			$sql  ="select * from order_views
			where 1 $condition order by order_id desc";
			$rows = $this->db->query($sql)->result_array();
			$data["rows"] = $rows;
		
			$this->template->load('admin/themes',"report/order_list",$data);
		
		
		
	}
	function order_list_print($type_id,$date1,$date2)
	{
		$user = $this->user->get();
	
	
		$condition =  "";
		$condition2 ="";
		$condition_text = array();
		$condition_text["type_name"] = "ALL";
		$condition_text["date1"] = "ALL";
		$condition_text["date2"] = "ALL";
	
	
	
		if($type_id)
		{
	
			$condition .=" and  type_id =".$type_id;
			$this->db->where("type_id",$type_id);
			$result = $this->db->get("materail_types")->result_array();
			$condition_text["type_name"] =$result[0]["type_name"];
	
		}
	
		if($date1)
		{
	
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date1'";
			$date = DateTime::createFromFormat('Y-m-d',$date1)->format('Y-m-d');
			$condition_text["date1"] =$date;
		}
		if($date2)
		{
	
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date2'";
			$date = DateTime::createFromFormat('Y-m-d',$date2)->format('Y-m-d');
			$condition_text["date2"] =$date;
		}
	
	
	
		//$this->template->set("report_name"," ใบรายงาน");
	
	
	
		$data["label"] = $condition_text;
		$this->template->set("report_date",date("d/m/Y"));
		//$this->template->set("no",$rows[0]["code"]);
	
		$sql  ="select * from order_views
		where 1 $condition order by order_id desc";
		$rows = $this->db->query($sql)->result_array();
		$data["rows"] = $rows;
	
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
	
		$this->template->load('report/report_theme',"report/order_list_print",$data);
	}
	function order_list()
	{
		$this->template->set("head_text", $this->user->get_h1());
		$this->form_validation->set_rules('status_id',  'สถานะ', 'trim');
		
		$this->form_validation->set_rules('date1',  '', 'trim|required');
		$this->form_validation->set_rules('date2',  '', 'trim|required');
		
		
		if ($this->form_validation->run() == FALSE)
		{
			$this->template->load('admin/themes',"report/order_list",null);
		}
		else
		{
		
			//redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
			$condition = "";
			if($_POST["type_id"])
			{
				$condition .= " and type_id = ".$_POST["type_id"];
			}
		
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date1"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date'";
			$data["date1"] = $date;
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date2"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date'";
		
			$data["date2"] = $date;
			$data["type_id"] = $_POST["type_id"];
		
		
			$sql  ="select * from order_views
			where 1 $condition order by order_id desc";
			$rows = $this->db->query($sql)->result_array();
			$data["rows"] = $rows;
		
			$this->template->load('admin/themes',"report/order_list",$data);
		}
		
	}
	function allocation_list()
	{
		
		$this->template->set("head_text", $this->user->get_h1());
		$this->form_validation->set_rules('status_id',  'สถานะ', 'trim');
		
		$this->form_validation->set_rules('date1',  '', 'trim|required');
		$this->form_validation->set_rules('date2',  '', 'trim|required');
		
		
		if ($this->form_validation->run() == FALSE)
		{
			$this->template->load('admin/themes',"report/allocation_list",null);
		}
		else
		{
		
			//redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
			$condition = "";
			if($_POST["status_id"])
			{
				
				if($_POST["status_id"]==1)
				{
					$condition = " and (select count(aiv.allocation_item_id) from allocation_item_views aiv
 							where aiv.allocation_id  = av.allocation_id and aiv.amount_allocation < 0) > 0";
				}
				if($_POST["status_id"]==2)
				{
					$condition = " and (select count(aiv.allocation_item_id) from allocation_item_views aiv
					where aiv.allocation_id  = av.allocation_id and aiv.amount_allocation > 0) > 0";
				}
			}
			if($_POST["place_id"])
			{
				$condition .=" and place_id = ".$_POST["place_id"];
			}	
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date1"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date'";
			$data["date1"] = $date;
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date2"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date'";
				
			$data["date2"] = $date;
			$data["status_id"] = $_POST["status_id"];
				
				
			$sql  ="select * from allocation_views av
 				 where 1 $condition order by av.allocation_id desc";
  			$rows = $this->db->query($sql)->result_array();
			$data["rows"] = $rows;
				
			$this->template->load('admin/themes',"report/allocation_list",$data);
		}
		
	}
	function allocation_list_print($status_id,$date1,$date2)
	{
		$user = $this->user->get();
	
	
		$condition =  "";
		$condition2 ="";
		$condition_text = array();
		$condition_text["status_name"] = "ALL";
		$condition_text["date1"] = "ALL";
		$condition_text["date2"] = "ALL";
	
	
	
		if($status_id)
		{
	
			$condition .=" and  status_id =".$status_id;
			$this->db->where("status_id",$status_id);
			$result = $this->db->get("maintain_status")->result_array();
			$condition_text["status_name"] =$result[0]["status_name"];
	
		}
	
		if($date1)
		{
				
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date1'";
			$date = DateTime::createFromFormat('Y-m-d',$date1)->format('Y-m-d');
			$condition_text["date1"] =$date;
		}
		if($date2)
		{
				
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date2'";
			$date = DateTime::createFromFormat('Y-m-d',$date2)->format('Y-m-d');
			$condition_text["date2"] =$date;
		}
	
	
	
		//$this->template->set("report_name"," ใบรายงาน");
	
	
	
		$data["label"] = $condition_text;
		$this->template->set("report_date",date("d/m/Y"));
		//$this->template->set("no",$rows[0]["code"]);
	
		$sql  ="select * from allocation_views
 				 where 1 $condition order by allocation_id desc";
  		$rows = $this->db->query($sql)->result_array();
		$data["rows"] = $rows;
	
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
	
		$this->template->load('report/report_theme',"report/allocation_list_print",$data);
	}
	function maintain_list_print($status_id,$date1,$date2)
	{
		$user = $this->user->get();
		
	
		$condition =  "";
		$condition2 ="";
		$condition_text = array();
		$condition_text["status_name"] = "ALL";
		$condition_text["date1"] = "ALL";
		$condition_text["date2"] = "ALL";
		
		
		
		if($status_id)
		{
		
			$condition .=" and  status_id =".$status_id;
			$this->db->where("status_id",$status_id);
			$result = $this->db->get("maintain_status")->result_array();
			$condition_text["status_name"] =$result[0]["status_name"];
		
		}
		
		if($date1)
		{
			
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date1'";
			$date = DateTime::createFromFormat('Y-m-d',$date1)->format('Y-m-d');
			$condition_text["date1"] =$date;
		}
		if($date2)
		{
			
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date2'";
			$date = DateTime::createFromFormat('Y-m-d',$date2)->format('Y-m-d');
			$condition_text["date2"] =$date;
		}
		
		
		
		//$this->template->set("report_name"," ใบรายงาน");
		
		
		
		$data["label"] = $condition_text;
		$this->template->set("report_date",date("d/m/Y"));
		//$this->template->set("no",$rows[0]["code"]);
		
		$sql = "select * from maintain_views where 1 $condition";
		$rows = $this->db->query($sql)->result_array();
		$data["rows"] = $rows;
		
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"admin/maintain_list_print",$data);
	}
	function maintain_list()
	{
		$this->template->set("head_text", $this->user->get_h1());
		$this->form_validation->set_rules('status_id',  'สถานะ', 'trim');
		
		$this->form_validation->set_rules('date1',  '', 'trim|required');
		$this->form_validation->set_rules('date2',  '', 'trim|required');
		
		
		if ($this->form_validation->run() == FALSE)
		{
			$this->template->load('admin/themes',"report/maintain_list",null);
		}
		else
		{
				
			//redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
			$condition = "";
			if($_POST["status_id"])
			{
				$condition .= " and status_id = ".$_POST["status_id"];
			}
			
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date1"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') >= '$date'";
			$data["date1"] = $date;
			$date = DateTime::createFromFormat('d/m/Y',$_POST["date2"])->format('Y-m-d');
			$condition .=" and date_format(create_date2,'%Y-%m-%d') <= '$date'";
			
			$data["date2"] = $date;
			$data["status_id"] = $_POST["status_id"];
			
			
			$sql = "select * from maintain_views where 1 $condition";
			$rows = $this->db->query($sql)->result_array();
			$data["rows"] = $rows;
			
			$this->template->load('admin/themes',"report/maintain_list",$data);
		}
	}
	function item_list_print()
	{
		$user = $this->user->get();
		
		$filter = $this->session->userdata("filter");
		$condition =  "";
		$condition2 ="";
		$condition_text = array();
		$condition_text["category_name"] = "ALL";
		$condition_text["type_name"] = "ALL";
		$condition_text["company_name"] = "ALL";
		$condition_text["budget_name"] = "ALL";
		
		
		if($filter["category_id"])
		{
		
			$condition .=" and  category_id =".$filter["category_id"];
			$this->db->where("category_id",$filter["category_id"]);
			$result = $this->db->get("category_views")->result_array();
			$condition_text["category_name"] =$result[0]["cat_base_name"]."/".$result[0]["category_name"];
				
		}
		if($filter["company_id"])
		{
		
			$condition .=" and  company_id =".$filter["company_id"];
				
			$this->db->where("company_id",$filter["company_id"]);
			$result = $this->db->get("company")->result_array();
			$condition_text["company_name"] =$result[0]["company_name"];
		}
		if($filter["budget_id"])
		{
		
			$condition .=" and  budget_id =".$filter["budget_id"];
				
			$this->db->where("budget_id",$filter["budget_id"]);
			$result = $this->db->get("budgets")->result_array();
				
			$condition_text["budget_name"] =$result[0]["budget_name"]." ".$result[0]["year"];
		}
		if($filter["type_id"])
		{
		
			$condition .=" and  type_id =".$filter["type_id"];
				
			$this->db->where("type_id",$filter["type_id"]);
			$result = $this->db->get("material_types")->result_array();
		
			$condition_text["type_name"] =$result[0]["type_name"];
				
				
		}
		if($filter["buy_date"])
		{
			$date = DateTime::createFromFormat('d/m/Y',$filter["buy_date"])->format('Y-m-d');
			$condition .=" and date_format(buy_date2,'%Y-%m-%d') >= '$date'";
			$condition_text["buy_date"] =$filter["buy_date"];
		}
		if($filter["buy_date2"])
		{
			$date = DateTime::createFromFormat('d/m/Y',$filter["buy_date2"])->format('Y-m-d');
			$condition .=" and date_format(buy_date2,'%Y-%m-%d') <= '$date'";
			$condition_text["buy_date2"] =$filter["buy_date2"];
		}
		if($filter["status_id"])
		{
			$condition2 = " and  status_id = ".$filter["status_id"];
			$this->db->where("status_id",$filter["status_id"]);
			$result = $this->db->get("material_item_status")->result_array();
			$condition_text["status_name"] =$result[0]["name"];
		}
		
		$sql = "select * from material_item_views where 1 $condition2 and material_id in (select material_id from inventory_views where 1 $condition)";
		
		$rows = $this->db->query($sql)->result_array();
		
		
		//$this->template->set("report_name"," ใบรายงาน");
		
		
		$data["rows"] = $rows;
		$data["label"] = $condition_text;
		$this->template->set("report_date",date("d/m/Y"));
		//$this->template->set("no",$rows[0]["code"]);
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/item_list_print",$data);
	}
	function register_list_print()
	{
		$user = $this->user->get();
		
		$filter = $this->session->userdata("filter");
		$condition =  "";
		$condition_text = array();
		$condition_text["category_name"] = "ALL";
		$condition_text["type_name"] = "ALL";
		$condition_text["company_name"] = "ALL";
		$condition_text["budget_name"] = "ALL";
		
		
		if($filter["category_id"])
		{
		
			$condition .=" and  category_id =".$filter["category_id"];
			$this->db->where("category_id",$filter["category_id"]);
			$result = $this->db->get("category_views")->result_array(); 
			$condition_text["category_name"] =$result[0]["cat_base_name"]."/".$result[0]["category_name"];
			
		}
		if($filter["company_id"])
		{
		
			$condition .=" and  company_id =".$filter["company_id"];
			
			$this->db->where("company_id",$filter["company_id"]);
			$result = $this->db->get("company")->result_array(); 
			$condition_text["company_name"] =$result[0]["company_name"];
		}
		if($filter["budget_id"])
		{
		
			$condition .=" and  budget_id =".$filter["budget_id"];
			
			$this->db->where("budget_id",$filter["budget_id"]);
			$result = $this->db->get("budgets")->result_array();
			
			$condition_text["budget_name"] =$result[0]["budget_name"]." ".$result[0]["year"];
		}
		if($filter["type_id"])
		{
		
			$condition .=" and  type_id =".$filter["type_id"];
			
			$this->db->where("type_id",$filter["type_id"]);
			$result = $this->db->get("material_types")->result_array();
				
			$condition_text["type_name"] =$result[0]["type_name"];
			
			
		}
		$rows = $this->db->query("select * from inventory_views where 1 $condition ")->result_array();
		
		
		//$this->template->set("report_name"," ใบรายงาน");
		
		
		$data["rows"] = $rows;
		$data["label"] = $condition_text;
		$this->template->set("report_date",date("d/m/Y"));
		//$this->template->set("no",$rows[0]["code"]);
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/register_list_print",$data);
	}
	function register_print($type,$id)
	{
		$user = $this->user->get();
		
		
		
			
		
		$this->db->where("material_id",$id);
		$rows = $this->db->get("material_views")->result_array();
		$data["row"] = $rows[0];
		$this->template->set("report_name","ใบลงทะเบียน".$rows[0]["type_name"]);
		
		
		$data["id"] = $id;
		$this->template->set("report_date",date("d/m/Y"));
		$this->template->set("no",$rows[0]["code"]);
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/register_print",$data);
	}
	function maintain_print($id)
	{
		$user = $this->user->get();
		
		
			$this->template->set("report_name","ใบส่งซ่อมบำรุงวัสดุค-รุภัณฑ์");
			
		
		$this->db->where("maintain_id",$id);
		$rows = $this->db->get("maintain_views")->result_array();
		$data["row"] = $rows[0];
	
		$data["id"] = $id;
		$this->template->set("report_date",date("d/m/Y"));
		$this->template->set("no",str_pad($rows[0]["maintain_id"],5,0,STR_PAD_LEFT));
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/maintain_print",$data);
	}
	
	
	
	function allocation($id,$type_id=1)
	{
		$user = $this->user->get();
		
		
			if($type_id==1)
			{
				$this->template->set("report_name","ใบขอเบิกจ่ายวัสดุ-ครุภัณฑ์");
			}else
			{
				$this->template->set("report_name","ใบเบิกจ่ายวัสดุ-ครุภัณฑ์");
			}
			
		
		$this->db->where("allocation_id",$id);
		$rows = $this->db->get("allocation_views")->result_array();
		$data["row"] = $rows[0];
		$data["type_id"] = $type_id;
		$data["id"] = $id;
		$this->template->set("report_date",date("d/m/Y"));
		$this->template->set("no",$rows[0]["code"]);
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/allocation_print",$data);
		
	}
	function order($type_id,$order_id)
	{
		
		$user = $this->user->get();
		if($type_id==1)
		{
			$this->template->set("report_name","ใบสั่งซื้อวัสดุ");
			
		}
		else 
		{
			$this->template->set("report_name","ใบสั่งซื้อวัสดุครุภัณฑ์");
			
		}
		$this->db->where("order_id",$order_id);
		$rows = $this->db->get("order_views")->result_array();
		$data["row"] = $rows[0];
		$data["type_id"] =$type_id;
		$data["order_id"] = $order_id;
		$this->template->set("report_date",date("d/m/Y"));
		$this->template->set("no",$rows[0]["code"]);
		$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
		
		$this->template->load('report/report_theme',"report/order_print",$data);
	}
	function dashboard()
	{
		$this->template->load('admin/themes',"report/dashboard",null);
		
	}
	function exp_list()
	{
		
		$this->template->load('admin/themes',"report/exp_list",null);
		
	}
	function maintain_company($id = 0,$offset =0)
	{
			if(!$id)
			{
				$this->form_validation->set_rules('company_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/maintain_company_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["company_id"],"location");
					
				}
			}
			else
			{
				
				$base = $this->user->current_controller()."/".$id;
				$this->db->where("company_id",$id);
				$cats =$this->db->get("company")->result_array();
				
				$data["head"] ="<h3>".$cats[0]["company_name"]."</h3>";
				$sql ="select * from maintain_history_views  where company_id = $id";
				$this->_page_query($sql,$base,"report/maintain_company_report",$data,true,30);
			}
	}
	function budget1($budget_id = 0)
	{
			if(!$budget_id)
			{
				$this->form_validation->set_rules('budget_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/budget_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["budget_id"]."/","location");
					
				}
			}
			else
			{
				
					$base = $this->user->current_controller()."/".$budget_id;
					
					$this->db->where("budget_id",$budget_id);
					$cats =$this->db->get("budgets")->result_array();
					
					$data["head"] ="<h3>".$cats[0]["budget_name"]."</h3>";
					$sql ="select * from  material_views where type_id = 2 and budget_id = $budget_id";
					$this->_page_query($sql,$base,"report/budget2",0,$data,true,30);
			}
	}
	function budget2($budget_id = 0)
	{
			if(!$budget_id)
			{
				$this->form_validation->set_rules('budget_id',  'งบประมาณ', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/budget_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["budget_id"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$budget_id;
					
					$this->db->where("budget_id",$budget_id);
					$cats =$this->db->get("budgets")->result_array();
					
					$data["head"] ="<h3>".$cats[0]["budget_name"]."</h3>";
					$sql ="select * from  material_views where type_id = 2 and budget_id = $budget_id";
					$this->_page_query($sql,$base,"report/budget2",0,$data,true,30);
			}
	}
	function page1()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page2()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page3()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page4()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page5()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page6()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page7()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page8()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page9()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page10()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page11()
	{
		$this->template->load("admin/themes","report/page",null);
	}
function page12()
	{
		$this->template->load("admin/themes","report/page",null);
	}	
	function index($offset=0)
	{
		
		 $condition = "";
		if($filter = $this->session->userdata("report_filter"))
		{
		if($filter["category_id"])
			{
			$condition = " and materials.category_id = ".$filter["category_id"];
			}
			if($filter["type_id"])
			{
			$condition = " and materials.category_id = ".$filter["type_id"];
			}
			if($filter["year"])
			{
			$condition = " and budgets.year = '".$filter["year"].".'";
			}
			
			if($filter["budget_id"])
			{
			$condition = " and budgets.budget_id = ".$filter["budget_id"];
			}
			
			if($filter["company_id"])
			{
			$condition = " and materials.company_id = ".$filter["company_id"];
			}
			
			if($filter["place_id"])
			{
			$condition = " and materials.place_id = ".$filter["place_id"];
			}
		}
		else
		{
			$filter =  Array ( "type_id" => 0, "category_id" => 0, "budget_id" => 0 ,"place_id" => 0 ,"company_id" => 0, "year" => 0 ) ;
			$this->session->set_userdata("report_filter");
		}
			
		
		$sql = "SELECT
		ref_code,
		materials.material_id,
		materials.`name`,
		materials.model,
		materials.`code`,
		materials.category_id,
		materials.buy_date,
		materials.buy_price,
		materials.brand,
		materials.warranty,
		materials.detail,
		materials.responsible,
		materials.thumbnail,
		materials.image2,
		materials.create_date,
		materials.last_modify,
		materials.`year`,
		materials.budget_id,
		materials.place_id,
		materials.company_id,
		budgets.budget_name,
		budgets.`year`,
		company.company_name,
		company.address,
		company.tel,
		place.place_name,
		place.remark,
		material_types.type_name,
		material_categories.category_name
		FROM
		materials
		LEFT JOIN budgets ON materials.budget_id = budgets.budget_id
		LEFT JOIN company ON materials.company_id = company.company_id
		left JOIN place ON materials.place_id = place.place_id
		LEFT JOIN material_types ON materials.type_id = material_types.type_id
		left JOIN material_categories ON materials.category_id = material_categories.category_id
		where 1  $condition"; 
				$base = "report/index";
		
		$this->_page_query($sql,$base,"report/report",$offset,array(),true,50);
		
	
	}
	function report_filter()
	{
		
		$this->session->set_userdata("report_filter",$_POST);
		redirect('report/index', 'location');
	
	}
function _page_query($sql,$base,$view="",$offset=0,$data= array(),$show =true,$limit = 9)
	{
		     
		$config['base_url'] = base_url()."index.php/$base/";
		$config['per_page'] = $limit;
		$config['total_rows'] = count($this->db->query($sql)->result_array());
		$this->pagination->initialize($config);
		
		$data['page_link'] =$this->pagination->create_links();
		$data['total'] = ceil( $config['total_rows']/$limit) ;
		
		$data['current_page'] = 1;
		
		if($offset){
		$data['current_page'] = ($offset/$limit)+1; 
		}
		
		$sql .=" limit $offset,$limit"; 
		$data['page'] =$this->pagination;
		
		$data['rows'] = $this->db->query($sql)->result_array();
		
		
		
		$data['paging'] = $this->load->view("paging",$data,true);	
		
		
		if($show){
		$this->template->load("admin/themes",$view,$data);
		}else
		{
			return $data;
		}
		
	}
function material2_by_year($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  inventory_views where type_id = 2 and year_value = $year";
					$this->_page_query($sql,$base,"report/material2_by_year",0,$data,true,30);
			}
}
function summary($year=0)
{
	if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from  material_summary_views where  year_value = $year";
					$this->_page_query($sql,$base,"report/summary",0,$data,true,30);
			}
}
function year_request_print($type_id,$year,$place_id)
{
	$user = $this->user->get();
	
	
	
		
	
	
	$this->template->set("report_name","รายงานความต้องการใช้วัสดุ-ครุภัณฑ์ประจำภาคการศึกษา ".$year);
	
	
	$data["type_id"] = $type_id;
	$data["year"] = $year;
	$data["place_id"] = $place_id;
	$this->template->set("report_date",date("d/m/Y"));
	
	$this->template->set("report_by",$user["firstname"]."  ".$user["lastname"]);
	
	$this->template->load('report/report_theme',"report/year_request_print",$data);
}
function m_request_by_year()
{
	
			
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
				$this->form_validation->set_rules('type_id',  '', 'trim|required');
				$this->form_validation->set_rules('place_id',  '', 'trim|required');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/m_request_by_year",null);
				}
				else
				{ 	
					
					//redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					$this->template->load('admin/themes',"report/m_request_by_year",null);
				}
			
}
function m_request_by_year1($year=0)
{
	
			$this->_m_request_by_year(1,$year);
}
function material1_by_year($year=0)
{
	
			if(!$year)
			{
				$this->form_validation->set_rules('year_value',  'ปีการศึกษา', 'trim|required|is_natural_no_zero');
	
				if ($this->form_validation->run() == FALSE)
				{
					$this->template->load('admin/themes',"report/material1_by_year_filter",null);
				}
				else
				{ 	
					
					redirect($this->user->current_controller()."/".$_POST["year_value"]."/","location");
					
				}
			}
			else
			{
				
				
					$base = $this->user->current_controller()."/".$year;
					
					$this->db->where("year_value",$year);
					
					$cats =$this->db->get("year_list")->result_array();
					
					$data["head"] ="<h3>ประจำปีการศึกษา ".$cats[0]["year_name"]."</h3>";
					
					
					$sql ="select * from inventory_views  where type_id = 1 and year_value = $year";
					$this->_page_query($sql,$base,"report/material1_by_year",0,$data,true,30);
			}
}
function table($id,$param1=null)
    {
    	$this->db->where("rb_id",$id);
    	
    	$rows = $this->db->get("report_builders")->result_array();
    	$data["row"] = $rows[0];
    	$codition ="";
    	$data["param1"] = "";
    	if(!is_null($param1))
    	{
    		$codition = " and ".str_ireplace("{param1}", $param1, $rows[0]["param"]);
    		$data["param1"] = $param1;
    	}
    	
    	
    	
    	$this->load->library('Tablebuilder');
    	
    	
    	
    	$sql = $rows[0]["sql_text"].$codition;
		$this->tablebuilder->set_query($sql);
		$data["result"] = 	$this->tablebuilder->get_table();
		$this->template->load('admin/themes',"report_builder/view",$data);
		
    }
    function export($method)
    {
    	
    	$this->db->where("webservice_method",$method);
    	$rows = $this->db->get("report_builders")->result_array();
    	$data["row"] = $rows[0];
    	$this->load->library('Tablebuilder');
		$this->tablebuilder->set_query($rows[0]["sql_text"]);
		header('Content-type: text/xml');
		echo  	$this->tablebuilder->get_xml();
		
    	
    }
    function add_report()
    {
    		$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql_text',  "sql text", 'trim|required');
			$this->form_validation->set_rules('report_name',  "Report name", 'trim|required');
			
			$this->form_validation->set_rules('webservice_method',  "", 'trim');
			$data = array();
			if ($this->form_validation->run() == FALSE)
			{
				$this->template->load('admin/themes',"report_builder/add_report");
			}
			else
			{
					$this->db->insert("report_builders",$_POST);
					$this->user->back_action();
			
			}
			
    }
   	function report_edit($id)
   	{
   		
   	$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql_text',  "sql text", 'trim|required');
			$this->form_validation->set_rules('report_name',  "Report name", 'trim|required');
			
			$this->form_validation->set_rules('webservice_method',  "", 'trim');
			$data = array();
			$this->db->where("rb_id",$id);
			if ($this->form_validation->run() == FALSE)
			{
				$rows = $this->db->get("report_builders")->result_array();
				$data["row"] = $rows[0];
				$this->template->load('admin/themes',"report_builder/report_edit",$data);
			}
			else
			{
					$this->db->update("report_builders",$_POST);
					$this->user->back_action();
			
			}
   	}
	function query_form()
	{
			$this->form_validation->set_message('required', 'กรุณาระบุ %s');
			$this->form_validation->set_rules('sql',  "sql text", 'trim|required');
			$data = array();
			if ($this->form_validation->run() == FALSE)
			{
				
			}
			else
			{
				$this->load->library('Tablebuilder');
				$this->tablebuilder->set_query($_POST["sql"]);
				
				
				$data["result"] = 	$this->tablebuilder->get_table();
			} 
			
			$this->template->load('admin/themes',"report_builder/query_form",$data);
		
	}
	/*
	public function all_report()
	{
		$data["rows"] = $this->db->get("report_builders")->result_array();
		$this->user->save_action();
		$this->template->load('admin/themes',"report_builder/report_list",$data);
		$this->user->save_action();
	}
	*/
	
}
?>